package dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import dao.DepartmentDAO;
import imageUtil.Page;
import model.Department;
import util.DBConnection;

public class DepartmentDAOImp implements DepartmentDAO {

	private  static final String findByCount = "select count(*) from department";
	
	private  static final String findByAll = "select * from department limit ?,?";
	
	private static final String findAll = "select * from department";
	
	private static final String findByName = "select DISTINCT id,department_name from department";
	
	private static final String insert = "insert into department(Department_Name,description) values(?,?)";
	
	private static final String updateDepartment = "update department set department_name=?,description=? where id=?";
	
	@Override
	public List<Department> findByAll(Page page) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		List<Department> reve = new ArrayList<Department>();
		Department i= null;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();	
			prep= conn.prepareStatement(findByAll);
			prep.setInt(1, page.getBeginIndex());
			prep.setInt(2, page.getEveryPage());
			rst= prep.executeQuery();
			while(rst.next()) {
				i= new Department();
				i.setId(rst.getInt("id"));
				i.setDepartmentName(rst.getString("department_name"));
				i.setDescription(rst.getString("description"));
				reve.add(i);
			} 
		}finally{
				DBConnection.close(prep, conn);
		}
		return reve;
			
	}

	@Override
	public int findByCount() throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		int count= 0;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findByCount);
			rst= prep.executeQuery();
			if(rst.next()) {
				count=rst.getInt(1);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return count; 
	}
	
	@Override
	public List<Department> findByName() throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		List<Department> reve1 = new ArrayList<Department>();
		ResultSet rst= null;
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(findByName);
			rst = prep.executeQuery();
			while(rst.next()){
				Department im = new Department();
				im.setId(rst.getInt("id"));
				im.setDepartmentName(rst.getString("department_name"));
				reve1.add(im);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
		return reve1;
	}

	@Override
	public boolean saveDepartment(String departmentName, String description) {
		PreparedStatement prep = null;
		Connection conn = null;
		int i = 0;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(insert);
			prep.setString(1, departmentName);
			prep.setString(2,description);
			i = prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBConnection.close(prep, conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(i != 0){
			return true;
		}else {
			return false;
		}
	}

	@Override
	public boolean updateDepartment(int id,String departmentName, String description) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		int i = 0;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(updateDepartment);
			prep.setString(1, departmentName);
			prep.setString(2,description);
			prep.setInt(3, id);
			i = prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBConnection.close(prep, conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(i != 0){
			return true;
		}else {
			return false;
		}	
	}

	@Override
	public List<Department> findByAll() throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		List<Department> reve = new ArrayList<Department>();
		Department i= null;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();	
			prep= conn.prepareStatement(findAll);
			rst= prep.executeQuery();
			while(rst.next()) {
				i= new Department();
				i.setId(rst.getInt("id"));
				i.setDepartmentName(rst.getString("department_name"));
				i.setDescription(rst.getString("description"));
				reve.add(i);
			} 
		}finally{
				DBConnection.close(prep, conn);
		}
		return reve;
	}

}
